package org.shj.spark.sql

import org.apache.spark.sql.SparkSession

object SparkSQLGroupTopN {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder().appName("SparkSQLGroupTopN").master("local").getOrCreate()
    
    spark.sparkContext.setLogLevel("WARN")
    val empDf = spark.read.json("E:/workspace/scala/sparkjava/src/main/resources/employees.json")
    
    empDf.createOrReplaceTempView("emp")
    
    val sqlStr = """select deptCd, salary 
                 from (select deptCd, salary, row_number() OVER (PARTITION BY deptCd order by salary DESC) rank 
                       from emp) tmp
                 where tmp.rank <= 2
                 """
    spark.sql(sqlStr).foreach(row => println(row))
    
    spark.stop()
            
  }
}